<html>
    <head>
        <title>Squiggle SQL Builder : Two Minute Tutorial</title>
        <link rel="stylesheet" href="style.css" type="text/css">
    </head>
    <body>

        <h1>Squiggle Two(ish) Minute Tutorial</h1>

        <p>This short tutorial will take you through the basics of using Squiggle.</p>

        <h2>Creating a basic SELECT query</h2>

        <p>The core of Squiggle revolves around the
        <code><a href="javadoc/com/truemesh/squiggle/SelectQuery.html">SelectQuery</a></code>
        class. When creating a new <code>SELECT</code> statement, you instantiate a new
        <code>SelectQuery</code> and add query attributes to it. </p>

        <p>Instances of <code><a href="javadoc/com/truemesh/squiggle/Table.html">Table</a></code>
        are used to represent each table participating in a query. <code>Table</code> takes a name
        as a parameter to the constructor, which is the name of the table in the database.</p>

        <p>When instantiating a <code>SelectQuery</code>, a <code>Table</code> must be passed to
        the constructor that represents the table that this statement will be SELECTing from.
        <i>(Note: it is possible to SELECT from multiple tables using joins or sub-selects, as we
        shall see later).</i></p>

<blockquote><pre><b>Table orders = new Table("orders_table");
SelectQuery select = new SelectQuery(orders);</b></pre></blockquote>

        <p>Now columns can be added to the query using <code>SelectQuery.addColumn()</code>. This
        takes two parameters; the <code>Table</code> containing the column and the name of the column in
        the database.</p>

<blockquote><pre><b>select.addColumn(orders, "id");
select.addColumn(orders, "total_price");</b></pre></blockquote>

        <p>The generated SQL can be obtained using <code>SelectQuery.toString()</code>.</p>

<blockquote><pre><b>System.out.println(select.toString());</b></pre></blockquote>

        <p>Which outputs:</p>

<blockquote><pre><b>SELECT
   orders_table.id ,
   orders_table.total_price
FROM
    orders_table</b></pre></blockquote>

        <h2>Adding WHERE criteria</h2>

        <p>The <code>SelectQuery.addCriteria()</code> method can be used to add criteria to the WHERE
        clause of a query, which takes a
        <code><a href="javadoc/com/truemesh/squiggle/Criteria.html">Criteria</a></code> object as a parameter.</p>

        <p><code>Criteria</code> itself is an abstract class as there are many different types of criteria that can
        be appended to a WHERE clause.</p>

        <p><code><a href="javadoc/com/truemesh/squiggle/MatchCriteria.html">MatchCriteria</a></code> is used for
        specifying a simple match such as <code>MY_COL = 'foo'</code> or <code>C &lt;= 6</code>.</p>

<blockquote><pre><b>select.addCriteria(new MatchCriteria(orders, "status", MatchCriteria.EQUALS, "processed"));
select.addCriteria(new MatchCriteria(orders, "items", MatchCriteria.LESS, 5));</b></pre></blockquote>

        <p>The parameters for <code>MatchCriteria</code> constructor are:</p>

        <ul>
            <li><code>Table table</code>: The table containing the column used in the match.</li>
            <li><code>String columnname</code>: The database column name in the table.</li>
            <li><code>String matchType</code>: The operator to use. MatchCriteria provides some constants for these.</li>
            <li><code>String/int/float/boolean value</code>: The value to match against. There are multiple overloadded
            constructors for different types.</li>
        </ul>

        <p>The query now looks like:</p>

<blockquote><pre>SELECT
   orders_table.id ,
   orders_table.total_price
FROM
    orders_table
<b>WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5</b></pre></blockquote>

        <p>Another type of <code>Criteria</code> is the
        <code><a href="javadoc/com/truemesh/squiggle/InCriteria.html">InCriteria</a></code>, which is used for
        specifiying <code>MY_COL IN ('a','b','c',....)</code> criteria. The values can be specified using an array
        or a collection.</p>

<blockquote><pre><b>select.addCriteria(new InCriteria(orders, "delivery", new String[] { "post", "fedex", "goat" } ));</b></pre></blockquote>

        <p>Which produces:</p>

<blockquote><pre>SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    <b>orders_table.delivery IN (
        'post','fedex','goat'
    )</b></pre></blockquote>

        <h2>Joining tables</h2>

        <p>To add a new table to the query using a join, a new <code>Table</code> must be instantiated and then
        passed to the <code>SelectQuery.addJoin()</code> method.</p>

        <p>The parameters for the <code>SelectQuery.addJoin()</code> are:</p>

        <ul>
            <li><code>Table sourceTable</code></li>
            <li><code>String sourceColumn</code></li>
            <li><code>Table destTable</code></li>
            <li><code>String destColumn</code></li>
        </ul>

        <p>So, this:</p>
<blockquote><pre><b>Table warehouses = new Table("warehouses_table");
select.addJoin(orders, "warehouse_id", warehouses, "id");</b></pre></blockquote>

        <p>Modifies the SELECT statement to insert the SQL necessary to include the join:</p>

<blockquote><pre>SELECT
    orders_table.id ,
    orders_table.total_price
FROM
    orders_table ,
    <b>warehouses_table</b>
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    <b>orders_table.warehouse_id = warehouses_table.id</b></pre></blockquote>

        <p>Of course, joining the table is pretty useless on its own, so you can also use the table in the query
        in the same way you could use the first table.</p>

<blockquote><pre><b>select.addColumn(warehouses, "location");
select.addCriteria(new MatchCriteria(warehouses, "size", MatchCriteria.EQUALS, "big"));</b></pre></blockquote>

        <p>Which adds a new column to the selection and a new match criteria:</p>

<blockquote><pre>SELECT
    orders_table.id ,
    orders_table.total_price ,
    <b>warehouses_table.location</b>
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    orders_table.warehouse_id = warehouses_table.id AND
    <b>warehouses_table.size = 'big'</b></pre></blockquote>

        <h2>Performing sub-selects</h2>

        <p>To perform a sub-select (query within query), you build a seperate query using a new instance of
        <code>SelectQuery</code>, and then add it to the first using an <code>InCriteria</code>.</p>

        <p>So, here's a new select statement:</p>

<blockquote><pre><b>Table offers = new Table("offers_table");
SelectQuery subSelect = new SelectQuery(offers);
subSelect.addColumn(offers, "location");
subSelect.addCriteria(new MatchCriteria(offers, "valid", MatchCriteria.EQUALS, true));</b></pre></blockquote>

        <p>Which, on its own, produces:</p>

<blockquote><pre><b>SELECT
    offers_table.location
FROM
    offers_table
WHERE
    offers_table.valid = true</b></pre></blockquote>

        <p>This can then be used as a sub-select in the original query by adding the new <code>SelectQuery</code>
        as a parameter to <code>InCriteria</code>:</p>

<blockquote><pre><b>select.addCriteria(new InCriteria(warehouses, "location", subSelect));</b></pre></blockquote>

        <p>Which leaves us with:</p>

<blockquote><pre>SELECT
    orders_table.id ,
    orders_table.total_price ,
    warehouses_table.location
FROM
    orders_table ,
    warehouses_table
WHERE
    orders_table.status = 'processed' AND
    orders_table.items < 5 AND
    orders_table.delivery IN (
        'post','fedex','goat'
    ) AND
    orders_table.warehouse_id = warehouses_table.id AND
    warehouses_table.size = 'big' AND
    <b>warehouses_table.location IN (
        SELECT
            offers_table.location
        FROM
            offers_table
        WHERE
            offers_table.valid = true

    )</b></pre></blockquote>

        <p>Phew!</p>


        <h2>Next steps</h2>

        <p>This concludes the two minute tutorial. Maybe it took a bit longer, but hopefully not that much.</p>

        <p>You should have a solid idea of how to use Squiggle now, including:</p>

        <ul>
            <li>Building SELECT statements.</li>
            <li>Adding WHERE criteria.</li>
            <li>Joining tables.</li>
            <li>Performing sub-selects.</li>
        </ul>

        <p>From here, the best way to learn more about Squiggle is by exploring the
        <a href="javadoc/index.html">API</a>, looking at the source code and experimenting.</p>

        <hr>

        <table width="100%" cellspacing="0" border="0">
            <tr>
                <td align="left" valign="bottom"><a href="@HOMEPAGE@">Squiggle Home Page</a></td>
                <td align="right" valign="bottom">
                    Joe Walnes,
                    &lt;<a href="mailto:joe@truemesh.com">joe@truemesh.com</a>&gt;
                    - <a href="http://joe.truemesh.com/">http://joe.truemesh.com/</a>
                </td>
            </tr>
        </table>

    </body>
</html>
